package ua.epam.muzalevskiy.traction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import org.apache.log4j.Logger;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotExistsParameterException;
import ua.epam.muzalevskiy.traction.dao.exceptions.NotUniqueParameterException;
import ua.epam.muzalevskiy.traction.user.Administrator;

/**
 * <code>AdministratorDaoImplementation</code> class is implementation of
 * <code>AdministratorDao</code> interface.
 *
 * @author Muzalevskiy Vitaliy
 */
public class AdministratorDaoImplementation implements AdministratorDao {

    private static final Logger LOGGER =
            Logger.getLogger(UserDaoImplementation.class);
    private final ConnectionFactory factory =
            ConnectionFactoryFactory.newConnectionFactory();
    
    /** Query for inserting new administrator to DataBase.*/
    public static final String ADD_ADMINISTRATOR_SQL = "INSERT INTO "
            + "traction.administrator VALUES( null, ?, ?, ?)";
    
    /** Query to select all administrators from DataBase.*/
    public static final String SELECT_ALL_SQL = "SELECT * FROM "
            + "traction.administrator";
    
    /** Query to select administrator with concrete ID value.*/
    public static final String SELECT_BY_ID_SQL = "SELECT * FROM "
            + "traction.administrator WHERE id = ?";
    
    /** Query to select administrator with concrete login value.*/
    public static final String SELECT_BY_NAME_SQL = "SELECT * FROM "
            + "traction.administrator WHERE user_name = ?";
    
    /** Query to select administrator with concrete email value.*/
    public static final String SELECT_BY_EMAIL_SQL = "SELECT * FROM "
            + "traction.administrator WHERE email = ?";
    
    /** Query to delete administrator with concrete ID value.*/
    public static final String DELETE_BY_ID_SQL = "DELETE FROM "
            + "traction.administrator WHERE id = ?";
    
    /** Query to delete administrator with concrete login and password value.*/
    public static final String SELECT_ADMINISTRATOR_SQL = "SELECT * FROM "
            + "traction.administrator WHERE user_name = ? AND user_password = ?";
    
    public static final String ATTRIBUTE_ID = "id";
    public static final String ATTRIBUTE_USER_NAME = "user_name";
    public static final String ATTRIBUTE_USER_PASSWORD = "user_password";
    public static final String ATTRIBUTE_EMAIL = "email";
    public static final String ATTRIBUTE_REAL_NAME = "real_name";
    public static final String ATTRIBUTE_LASTNAME = "lastname";
    
    static {
        ConnectionFactoryFactory.setType(ConnectionFactoryFactory.FactoryType.C3P0);
    }

    /**
     * Method need to save information about new administrator in DataBase.
     * 
     * @param query <code>PreparedStatement</code> value that include all
     *      information that need to be saved in DataBase.
     * @throws SQLException if there was some problems with Query.
     */
    private void doInsert(PreparedStatement query, String name,
            String password, String email, String realName, String lastname) 
            throws SQLException {
        query.setString(1, name);
        query.setString(2, password);
        query.setString(3, email);
        query.setString(4, realName);
        query.setString(5, lastname);
        query.executeUpdate();
        LOGGER.info("New Administrator info was put to DataBase! "
                + "(user_name = " + name + ")");
    }

    /**
     * Method for getting information about administrator from DataBase.
     * 
     * @param result <code>ResultSet</code> value of concrete query.
     * @return <code>Administrator</code> value.
     * @throws SQLException if there was some problems with Query.
     */
    private Administrator createAdministrator(ResultSet result) throws SQLException {
        int id = result.getInt(ATTRIBUTE_ID);
        String name = result.getString(ATTRIBUTE_USER_NAME);
        String password = result.getString(ATTRIBUTE_USER_PASSWORD);
        String email = result.getString(ATTRIBUTE_EMAIL);
        String realName = result.getString(ATTRIBUTE_REAL_NAME);
        String lastname = result.getString(ATTRIBUTE_LASTNAME);
        LOGGER.debug("Administrator info was taken from DataBase! "
                + "(user_name = " + name + ")");
        return new Administrator(id, name, password, email, realName, lastname);
    }
    
    @Override
    public void addAdministrator(Administrator administrator) 
            throws NotUniqueParameterException {
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(ADD_ADMINISTRATOR_SQL,
                        Statement.RETURN_GENERATED_KEYS);

                myConnection.setAutoCommit(false);
                if (selectAdministratorByName(administrator.getName()) != null) {
                    throw new NotUniqueParameterException();
                }
                if (selectAdministratorByEmail(administrator.getEmail()) != null) {
                    throw new NotUniqueParameterException();
                }
                try {
                    doInsert(query, administrator.getName(), 
                            administrator.getPassword(), 
                            administrator.getEmail(),
                            administrator.getRealName(),
                            administrator.getLastname());
                    ResultSet resSet = query.getGeneratedKeys();
                    if (resSet.next()) {
                        LOGGER.info("ID of inserted user "
                                + new Integer(resSet.getInt(1)).toString());
                    }
                    query.close();
                    myConnection.commit();
                } catch (SQLException ex) {
                    LOGGER.warn(ex.getMessage());
                    LOGGER.info("Trtansaction is rolled back!");
                    myConnection.rollback();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public List<Administrator> selectAll() {
        List<Administrator> administratorsList = new LinkedList();
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                Statement statement = myConnection.createStatement();
                ResultSet result = statement.executeQuery(SELECT_ALL_SQL);
                while (result.next()) {
                    Administrator user = createAdministrator(result);
                    administratorsList.add(user);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return administratorsList;
    }

    @Override
    public Administrator selectAdministratorByID(int userID) {
        Administrator administrator = null;
        Connection myConnection = null;
        try {
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_ID_SQL);
                query.setInt(1, userID);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    administrator = createAdministrator(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return administrator;
    }

    @Override
    public Administrator selectAdministratorByName(String userName) {
        Administrator administrator = null;
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_NAME_SQL);
                query.setString(1, userName);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    administrator = createAdministrator(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return administrator;
    }

    @Override
    public Administrator selectAdministratorByEmail(String email) {
        Administrator administrator = null;
        try {
            Connection myConnection = factory.newConnection();
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_BY_EMAIL_SQL);
                query.setString(1, email);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    administrator = createAdministrator(result);
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return administrator;
    }

    @Override
    public void deleteAdministratorByID(int userID) 
            throws NotExistsParameterException {
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(DELETE_BY_ID_SQL);
                myConnection.setAutoCommit(false);
                Administrator user = selectAdministratorByID(userID);
                if (user == null) {
                    throw new NotExistsParameterException();
                }
                query.setInt(1, userID);
                query.executeUpdate();
                query.close();
                myConnection.commit();
                LOGGER.info("User info was cleared from DataBase! (userID = "
                        + userID + ")");
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
    }

    @Override
    public Administrator getAdministrator(String userName, String userPassword) 
            throws NotExistsParameterException {
        Administrator administrator = null;
        try {
            Connection myConnection = null;
            try {
                myConnection = factory.newConnection();
                PreparedStatement query = myConnection
                        .prepareStatement(SELECT_ADMINISTRATOR_SQL);
                query.setString(1, userName);
                query.setString(2, userPassword);
                ResultSet result = query.executeQuery();
                while (result.next()) {
                    administrator = createAdministrator(result);
                }
                if (administrator == null) {
                    throw new NotExistsParameterException();
                }
            } finally {
                if (myConnection != null) {
                    myConnection.close();
                }
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.getMessage());
        }
        return administrator;
    }
    
}
